package com.carlinfo.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class DbUtil
{	
	private static DbUtil INSTANCE = new DbUtil();
	private String driver = ConstantFinalUtil.bundle.getString("jdbc.driver");
	private String url = ConstantFinalUtil.bundle.getString("jdbc.url");
	private String user = ConstantFinalUtil.bundle.getString("jdbc.user");
	private String password = ConstantFinalUtil.bundle.getString("jdbc.password");
	public static DbUtil getInstance()
	{
		return INSTANCE;
	}
	/*标准的jdbc*/
	public void standardJDBC()
	{
		String driver = "com.mysql.jdbc.Driver";
		String url ="jdbc:mysql:///userscenter?useSSL=false";
		String user ="root";
		String password="123456";
		
		
		String sql ="select*from a_dynasty;";
		Connection connection = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
			pst = connection.prepareStatement(sql);
			rs = pst.executeQuery();
			System.out.println("id\t名字\t首都\t国祚\t状态\t开国时间\t\t\t创建时间\t\t\t更新时间\t\t\t发布时间");
			while(rs.next())
			{
				int id = rs.getInt("id");
				String name =rs.getString("name");
				String capital =rs.getString("capital");
				short age = rs.getShort("age");
				Date st = rs.getTimestamp("st");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTimestamp("pubTime");
				byte status = rs.getByte("status");
				System.out.println(id+"\t"+name+"\t"+capital+"\t"+age+"\t"+status+"\t"+st.toLocaleString()+"\t"+createTime.toLocaleString()+"\t"+updateTime.toLocaleString()+"\t"+pubTime.toLocaleString());
				
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			try
			{
				if (rs!=null)
				{
					rs.close();
					rs=null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(pst!=null)
				{
					pst.close();
					pst = null;
				}
			} catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (connection!=null)
				{
					connection.close();
					connection=null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	/* jdbc 封装*/
	

	private DbUtil()
	{
		try
		{
			Class.forName(driver);
		} catch (ClassNotFoundException e)
		{
			System.out.println("=加载驱动失败=");
			e.printStackTrace();
		}
		
	}
	public Connection getConn()
	{
		try
		{
			Connection connection =DriverManager.getConnection(url, user, password);
			return connection;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}
	public void closeAll(Connection connection,Statement st,ResultSet rs)
	{
		try
	 			{
	 				/* 倒着关 */
	 				if(rs != null)
	 				{
	 					rs.close();
	 					rs = null ; 
	 				}
	 			} catch (SQLException e)
	 			{
	 				e.printStackTrace();
	 			}
	 			
	 			try
	 			{
	 				/* 倒着关 */
	 				if(st != null)
	 				{
	 					st.close();
	 					st = null ; 
	 				}
	 			} catch (SQLException e)
	 			{
	 				e.printStackTrace();
	 			}
	 			
	 			try
	 			{
	 				/* 倒着关 */
	 				if(connection != null)
	 				{
	 					connection.close();
	 					connection = null ; 
	 				}
	 			} catch (SQLException e)
	 			{
	 				e.printStackTrace();
	 			}
	}
	/*使用封装的jdbc来操作*/

	public void newJDBC()
	{
		String sql = "select*from a_dynasty;";
		DbUtil dbUtil = DbUtil.getInstance();
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection conn = dbUtil.getConn();
		try
	 		{
	 			/* 创建语句 */
	 			pst = conn.prepareStatement(sql);
	 			rs = pst.executeQuery();
	 			
	 			/* 查询一条:if,还是查询多条:while */
	 			System.out.println("id\t名字\t首都\t国祚\t状态\t开国时间\t\t\t创建时间\t\t\t更新时间\t\t\t发布时间");
	 			while(rs.next())
	 			{
	 				/* 取值
	 				 * 整数:第几列
	 				 * 字符串:别名
	 				 *  */
	 				int id = rs.getInt("id");
	 				String name = rs.getString("name");
	 				String capital = rs.getString("capital");
	 				short age = rs.getShort("age");
	 				/* 时间戳 */
	 				Date st = rs.getTimestamp("st");
	 				Date createTime = rs.getTimestamp("createTime");
	 				Date updateTime = rs.getTimestamp("updateTime");
	 				Date pubTime = rs.getTimestamp("pubTime");
	 				byte status = rs.getByte("status");
	 				/* 打印 */
	 				System.out.println(id + "\t" + name + "\t" + capital + "\t"
	 						+ age + "\t" + status + "\t" + st.toLocaleString() + "\t"
	 						+ createTime.toLocaleString() + "\t" + updateTime.toLocaleString() 
	 						+ "\t" + pubTime.toLocaleString());
	 			}
	 		} catch (SQLException e)
	 		{
	 			e.printStackTrace();
	 		}finally
	 		{
	 			dbUtil.closeAll(conn, pst, rs);
	 	}
	}
	
	public static void main(String[] args)
	{
			DbUtil dbUtil = DbUtil.getInstance();
			//dbUtil.standardJDBC();
			dbUtil.newJDBC();
		
	}
}
